<?php

namespace App\Libs\Company\SheetTemplateHandler\Aggregate;

use App\Libs\Company\SheetTemplateHandler\Manager;
use App\Models\AggregateReport;
use App\Models\SheetTemplate;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class Template217 extends AbstractTemplate
{
    public $dataStartRow = 2;

    public $dataEndRow = 2;

    protected $lastMonthData;

    protected $lastYearData;

    protected static $monthColumnMap = [
        '1' => 4,
        '2' => 6,
        '3' => 8,
        '4' => 10,
        '5' => 12,
        '6' => 14,
        '7' => 16,
        '8' => 18,
        '9' => 20,
        '10' => 22,
        '11' => 24,
        '12' => 26,
    ];

    public function generateAbstract()
    {
        $this->renderHeader();
        $this->output['sheets']['sheet-01']['cellData']['0']['0'] = $this->createTitleCell("日喀则市{$this->task->year}年\r{$this->template->name}");

        $this->lastMonthData = $this->getLastMonthData();
        $this->lastYearData = $this->getLastYearData();

        $currStartRow = $this->dataStartRow;
        $sn = 1;
        foreach ($this->task->subTasks as $subTask) {
            $handler = Manager::createReportHandler(SheetTemplate::find($this->task->sheet_template_id), $subTask);
            $data = $handler->getData();

            foreach ($data as $row) {
                if (empty($row[1]['v'])) {
                    continue;
                }
                $newRow = [
                    ['v' => str_replace('旅游局', '', $subTask->district->name), 's' => 'CENTER'],
                    ['v' => $sn++, 's' => 'CENTER'],
                    ['v' => $row[1]['v'], 't' => 2],
                    ['v' => $row[2]['v'], 't' => 2],
                ];
                foreach (self::$monthColumnMap as $month => $column) {
                    if ($month == $this->task->key) {
                        $newRow[$column] = ['v' => $row[3]['v'], 't' => 2];
                        break;
                    }
                    if (isset($this->lastMonthData[$newRow[2]['v']])) {
                        $newRow[$column] = ['v' => $this->lastMonthData[$newRow[2]['v']][$column]['v'] ?? '0', 't' => 2];
                    }
                }
                $currRow = $this->dataStartRow + $sn - 1;
                $newRow[28] = ['f' => "=E{$currRow}+G{$currRow}+I{$currRow}+K{$currRow}+M{$currRow}+O{$currRow}+Q{$currRow}+S{$currRow}+U{$currRow}+W{$currRow}+Y{$currRow}+AA{$currRow}"];
                $this->output['sheets']['sheet-01']['cellData'][] = $newRow;
            }
            $this->dataEndRow = $this->dataStartRow + $sn - 2;

            $this->output['sheets']['sheet-01']['mergeData'][] = [
                'startRow' => $currStartRow,
                'endRow' => $this->dataEndRow,
                'startColumn' => 0,
                'endColumn' => 0,
            ];
            $currStartRow = $this->dataEndRow + 1;
        }

        $this->renderFooter();
        return $this->output;
    }

    public function getLastMonthData()
    {
        if ($this->task->key == 1) {
            $year = $this->task->year;
            $key = 1;
        } else {
            $year = $this->task->year;
            $key = (int) $this->task->key - 1;
        }

        $lastAggregateReport = AggregateReport::where([
            'sheet_template_id' => $this->template->id,
            'frequency' => $this->task->frequency,
            'year' => $year,
            'key' => $key,
        ])
            ->first();
        if (!$lastAggregateReport) {
            return [];
        }
        $data = [];
        while (1) {
            $row = $lastAggregateReport->data['sheets']['sheet-01']['cellData'][$this->dataEndRow++] ?? [];
            if (!isset($row[1]['v']) || !is_numeric($row[1]['v'])) {
                break;
            }
            $data[$row[2]['v']] = $row;
        }
        return $data;
    }

    public function getLastYearData()
    {
        $lastAggregateReport = AggregateReport::where([
            'sheet_template_id' => $this->template->id,
            'frequency' => $this->task->frequency,
            'year' => $this->task->year - 1,
            'key' => '12',
        ])
            ->first();
        if (!$lastAggregateReport) {
            return [];
        }
        $data = [];
        while (1) {
            $row = $lastAggregateReport->data['sheets']['sheet-01']['cellData'][$this->dataEndRow++] ?? [];
            if (!isset($row[1]['v']) || !is_numeric($row[1]['v'])) {
                break;
            }
            $data[$row[2]['v']] = $row;
        }
        return $data;
    }
}
